import pandas as pd
import xlwt
import datetime


class ExcelWriter:
    def __init__(self, data, filename):
        self.data = data
        self.filename = filename
        self.normal_font = 'font: height 260;'
        self.blue_font = 'font: height 260, color_index blue;'
        self.red_font = 'font: height 260, color_index red;'
        self.bold_font = 'font: height 260, bold on;'
        self.yellow_background = 'pattern: pattern solid, fore_colour yellow; borders: bottom thin;'

    def write(self):
        wb = xlwt.Workbook()
        ws = wb.add_sheet('Sheet1')
        for i in range(len(self.data)):
            dt = datetime.datetime.strptime(self.data[i][0], '%Y-%m-%d %H:%M:%S')
            if dt.minute % 5 == 0:
                postfix = self.yellow_background
            else:
                postfix = ''
            ws.write(i, 0, self.data[i][0], xlwt.easyxf(self.bold_font))
            ws.write(i, 1, self.data[i][1], xlwt.easyxf(self.red_font))
            ws.write(i, 2, self.data[i][2], xlwt.easyxf(self.blue_font + postfix))
            ws.write(i, 3, self.data[i][3], xlwt.easyxf(self.blue_font + postfix))
            ws.write(i, 4, self.data[i][4], xlwt.easyxf(self.normal_font))
            ws.write(i, 5, self.data[i][5], xlwt.easyxf(self.normal_font))
            ws.write(i, 6, self.data[i][6], xlwt.easyxf(self.normal_font))
            ws.write(i, 7, self.data[i][7], xlwt.easyxf(self.normal_font))
        # set col width
        width1 = 6000
        width2 = 3400
        ws.col(0).width = width1
        ws.col(1).width = width2
        ws.col(2).width = width2
        ws.col(3).width = width2
        wb.save(self.filename)


df = pd.read_excel('a.xls', 'Sheet1')
writer = ExcelWriter(df.values, 'b.xls')
print(writer.data)
writer.write()
